
import java.sql.*;
import java.util.*;

class PersonsDAO {
  Connection con;
  private ArrayList<PersonalInfo> personList = new ArrayList<PersonalInfo>();

  public PersonsDAO(){
     getOracleConnection();
  }
  public Connection getOracleConnection(){
    String url="jdbc:odbc:orcl";
    String user="hr";
    String pass="hr";

    try{
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    }catch(java.lang.ClassNotFoundException e){
      System.err.println("ClassNotFoundException :" + e.getMessage());
    }

    try{
      con = DriverManager.getConnection(url, user, pass);
    }catch(SQLException ex){
      System.err.println("Sqlexception: "+ex.getMessage());
    }
    return con;
  }

  public ArrayList<PersonalInfo> searchPerson(String name){
    name =name.trim();
    
    if (name.length()==0) {
      System.out.println("Fill the name field!");
      
    }
    int id;
    double salary = 0;
    String firstName="";
    String lastName="";
    String email=""; 
     String selectString = "Select * from employees where last_Name like '%" + name+"%'"; 
     try{
       Statement stmt = con.createStatement();
       ResultSet rs=stmt.executeQuery(selectString);   
       while(rs.next()){
          id=rs.getInt("employee_id");
          firstName=rs.getString("first_name");
          lastName=rs.getString("last_name");
          salary=rs.getDouble("salary");
          email=rs.getString("email");
          PersonalInfo person = new PersonalInfo(id, firstName, lastName, salary, email);
          personList.add(person);
      }

     } catch (Exception ex) {
      System.err.println("exception: "+ex.getMessage());
    }
    return personList;
  }

}
